//
//  NewsDbOperate.m
//  MobilePlatform
//
//  Created by Raphael peter on 11-8-29.
//  Copyright 2011 __MyCompanyName__. All rights reserved.
//

#import "DBUtil.h"

@implementation DBUtil
//打开数据库
- (BOOL)openDB {
//	NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
//	NSString *documentsDirectory = [paths objectAtIndex:0];
	NSString *path = [kDocumentFolder stringByAppendingPathComponent:@"hubu003.sql"];
    NSFileManager *fileManager = [NSFileManager defaultManager];
    BOOL find = [fileManager fileExistsAtPath:path];
	
    //找到数据库文件cjpress.sql
    if(find) {
        //NSLog(@"Database file have already existed.");
        if(sqlite3_open([path UTF8String], &database_) != SQLITE_OK) {
            NSLog(@"Error: open database failure.");
            return NO;
        }
        return YES;
    }
    
    //直接打开数据库
    if(sqlite3_open([path UTF8String], &database_) == SQLITE_OK) {
        [self createTable:database_];
        return YES;
    } else {
        sqlite3_close(database_);
        NSLog(@"Error: create database failure.");
        return NO;
    }
    return NO;
}

//关闭数据库
- (BOOL)closeDB {
	sqlite3_close(database_);
	return YES;
}

//创建表
- (BOOL)createTable:(sqlite3 *)db {
    [self createTableBook:db];
    [self createTableNews:db];
    [self createTableSetting:db];
    [self createTableTxtPages:db];
    [self createTableAd:db];
    return YES;
}

- (BOOL)createTableBook:(sqlite3 *)db {
	char *sql = "create table if not exists cjp_book(\
    pk_id integer PRIMARY KEY AUTOINCREMENT NOT NULL, \
	book_id varchar2(32),\
	book_code varchar2(32),\
	book_name varchar2(255),\
	book_english_name varchar2(255),\
	book_editor_info text,\
	book_content_info text,\
	book_author_info text,\
	book_catalog text,\
	book_free_content text,\
	book_icon varchar2(64),\
	book_icon_small varchar2(64),\
	book_shot varchar2(64),\
	book_phone varchar2(64),\
	book_phone_type varchar2(16),\
	book_pad varchar2(64),\
	book_pad_type varchar2(16),\
	book_series varchar2(64),\
	book_price varchar2(16),\
	book_price2 varchar2(16),\
	book_price_print varchar2(16),\
	book_language varchar2(32),\
	book_author varchar2(64),\
	book_translator varchar2(64),\
	book_press varchar2(64),\
	book_issn varchar2(32),\
	book_press_time varchar2(32),\
	book_print_time varchar2(32),\
	book_pages integer,\
	book_words varchar2(16),\
	book_score integer,\
	book_category varchar2(32),\
	book_category1 varchar2(64),\
	book_hot varchar2(8),\
	book_star integer,\
	book_favorite varchar2(8),\
	book_state varchar2(16),\
	book_approval_time varchar2(16),\
	book_down_flag varchar2(8),\
	book_down_count integer,\
	book_focus_count integer,\
	book_comment_count integer,\
	book_icon_flag varchar2(8),\
	book_buy_flag varchar2(8),\
	book_mark varchar2(255),\
	book_delete varchar2(8),\
	book_ext_1 varchar2(64),\
	book_ext_2 varchar2(64),\
	book_ext_3 varchar2(64),\
	book_ext_4 varchar2(64),\
	book_ext_5 varchar2(64),\
	book_ext_6 varchar2(64),\
	book_ext_7 varchar2(64),\
	book_ext_8 varchar2(64),\
	book_ext_9 varchar2(64),\
	book_ext_10 varchar2(64)\
	)";
    sqlite3_stmt *statement;
    if(sqlite3_prepare_v2(db, sql, -1, &statement, nil) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement:create table cjp_book");
        return NO;
    }
    int success = sqlite3_step(statement);
    sqlite3_finalize(statement);
    if(success != SQLITE_DONE) {
        NSLog(@"Error: failed to create:CREATE TABLE cjp_book");
        return NO;
    }
    NSLog(@"Create table cjp_book successed.");
	return YES;
}


- (BOOL)createTableNews:(sqlite3 *)db {
	char *sql = "create table if not exists cjp_news(\
    pk_id integer PRIMARY KEY AUTOINCREMENT NOT NULL, \
	news_id varchar2(32),\
	news_title varchar2(255),\
	news_type varchar2(64),\
	news_content text,\
	news_date varchar2(64),\
	ext_1 varchar2(64),\
	ext_2 varchar2(64),\
	ext_3 varchar2(64),\
	ext_4 varchar2(64),\
	ext_5 varchar2(64),\
	ext_6 varchar2(64),\
	ext_7 varchar2(64),\
	ext_8 varchar2(64),\
	ext_9 varchar2(64),\
	ext_10 varchar2(64)\
	)";
    sqlite3_stmt *statement;
    if(sqlite3_prepare_v2(db, sql, -1, &statement, nil) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement:create table cjp_news");
        return NO;
    }
    int success = sqlite3_step(statement);
    sqlite3_finalize(statement);
    if(success != SQLITE_DONE) {
        NSLog(@"Error: failed to create:CREATE TABLE cjp_news");
        return NO;
    }
    NSLog(@"Create table cjp_news successed.");
	return YES;
}


- (BOOL)createTableSetting:(sqlite3 *)db {
	char *sql = "create table if not exists cjp_setting(\
	set_id varchar2(64) PRIMARY KEY,\
	set_value varchar2(255),\
	set_default varchar2(255))";
    sqlite3_stmt *statement;
    if(sqlite3_prepare_v2(db, sql, -1, &statement, nil) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement:create table cjp_setting");
        return NO;
    }
    int success = sqlite3_step(statement);
    sqlite3_finalize(statement);
    if(success != SQLITE_DONE) {
        NSLog(@"Error: failed to create:CREATE TABLE cjp_book");
        return NO;
    }
    NSLog(@"Create table cjp_setting successed.");
	return YES;
}

//book_orient: V 竖屏；H 横屏；C 目录；M 书签
// C和M时，title，range_location 可用
- (BOOL)createTableTxtPages:(sqlite3 *)db {
    static char *sql = "create table if not exists cjp_txtpages(\
    pk_id integer PRIMARY KEY AUTOINCREMENT NOT NULL, \
	book_id varchar2(32), \
    book_orient varchar2(16), \
    font_size integer, \
	range_location integer, \
    range_length integer, \
	title varchar2(512), \
	memo varchar2(512), \
	ext_1 varchar2(64), \
	ext_2 varchar2(64), \
	ext_3 varchar2(64))";
    sqlite3_stmt *statement;
    if(sqlite3_prepare_v2(db, sql, -1, &statement, nil) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement:create table cjp_txtpages");
        return NO;
    }
    int success = sqlite3_step(statement);
    sqlite3_finalize(statement);
    if(success != SQLITE_DONE) {
        NSLog(@"Error: failed to create:CREATE TABLE cjp_txtpages");
        return NO;
    }
    NSLog(@"Create table cjp_txtpages successed.");
	return YES;
}

//关闭数据库
- (BOOL)createTableAd {
	[self createTableAd:database_];
	return YES;
}

- (BOOL)createTableAd:(sqlite3 *)db {
	char *sql = "create table if not exists cjp_ad(\
    pk_id integer PRIMARY KEY AUTOINCREMENT NOT NULL, \
	ad_id varchar2(32),\
    ad_title varchar2(255),\
	ad_abstract text,\
	ad_content text,\
	ad_4_phone varchar2(16),\
	ad_4_pad varchar2(16),\
	image_4_phone varchar2(64),\
	image_4_pad varchar2(64),\
	ad_state varchar2(16),\
	ad_image_flag varchar2(8),\
	ext_1 varchar2(64),\
	ext_2 varchar2(64),\
	ext_3 varchar2(64),\
	ext_4 varchar2(64),\
	ext_5 varchar2(64)\
	)";
    sqlite3_stmt *statement;
    if(sqlite3_prepare_v2(db, sql, -1, &statement, nil) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement:create table cjp_ad");
        return NO;
    }
    int success = sqlite3_step(statement);
    sqlite3_finalize(statement);
    if(success != SQLITE_DONE) {
        NSLog(@"Error: failed to create:CREATE TABLE cjp_ad");
        return NO;
    }
    NSLog(@"Create table cjp_ad successed.");
	return YES;
}

- (BOOL) insertData:(NSArray *)books{
    char *msg;
    //NSData* ImageData = UIImagePNGRepresentation( channel.image_);
    //NSInteger Imagelen = [ImageData length];
    sqlite3_stmt *statement;
    static char *sql = "INSERT INTO cjp_book (\
    book_id, book_code, book_name, book_icon_small, book_phone,\
    book_phone_type, book_pad, book_pad_type, book_price, book_price2, \
    book_author, book_translator, book_category, book_category1, book_hot, \
    book_star,book_down_count, book_focus_count, book_approval_time, book_comment_count, book_state) \
    VALUES(?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?,'1')";
    
    static char *sqlcheck = "select book_id from cjp_book where book_id = ?";
    
    static char *sqlupdate = "update cjp_book set book_state = '1', \
    book_code = ?, book_name = ?, book_icon_small = ?, book_phone = ?,\
    book_phone_type = ?, book_pad = ?, book_pad_type = ?, book_price = ?, book_price2 = ?, \
    book_author = ?, book_translator = ?, book_category = ?, book_category1 = ?, book_hot = ?, \
    book_star = ?,book_down_count = ?, book_focus_count = ?, book_approval_time = ?, book_comment_count = ?, \
    book_down_flag = null \
    where book_id = ?";
    
    sqlite3_exec(database_, "begin transaction", NULL, NULL, &msg);
    
    for (int i=0; i<[books count]; i++) {
        BookItem *bookItem = [books objectAtIndex:i];
        
        BOOL isExistBook = FALSE;
        NSString *bookBuyFlag = @"0";//图书是否购买，如果已经购买，则下架图书不影响
        
        //检查是否存在任务
        if (sqlite3_prepare_v2(database_, sqlcheck, -1, &statement, NULL) != SQLITE_OK) {
            NSLog(@"Error: failed to prepare statement with message:insert book begin check.");
            return NO;
        }
        sqlite3_bind_text(statement, 1, [[bookItem bookId] UTF8String], -1, SQLITE_TRANSIENT); 
        if (sqlite3_step(statement) == SQLITE_ROW) {
            isExistBook = TRUE;
        }
        sqlite3_finalize(statement);
        
        //判断是否下架图书
        //bookExt2为1时是下架图书
        if(bookItem.bookExt2 != nil && [bookItem.bookExt2 isEqualToString:@"1"]){
            //如果该图书已经购买，则不进行下架处理
            if([bookBuyFlag isEqualToString:@"1"]){
                continue;
            }
            
            static char *sqlOutSale = "update cjp_book set book_state = '9' where book_id = ?";
            
            int success4 = sqlite3_prepare_v2(database_, sqlOutSale, -1, &statement, NULL);
            if (success4 != SQLITE_OK) {
                NSLog(@"Error: failed to update: cjp_book.book_state = '9'");
                return NO;
            }
            sqlite3_bind_text(statement, 1, [[bookItem bookId] UTF8String], -1, SQLITE_TRANSIENT); 
            success4 = sqlite3_step(statement);
            sqlite3_finalize(statement);
            
            if (success4 == SQLITE_ERROR) {
                NSLog(@"Error: failed to update cjp_book.book_state = '9' with message.");
                return NO;
            }
            
            continue;
        }
        
        //如果本地表中不存在数据，则插入数据
        if(!isExistBook) {
            
            int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);
            if (success != SQLITE_OK) {
                NSLog(@"Error: failed to insert: books");
                return NO;
            }
            
            
            int bindIndex = 1;
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookId == nil ? @"" : bookItem.bookId) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookCode == nil ? @"" : bookItem.bookCode) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookName == nil ? @"" : bookItem.bookName) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookIconSmall == nil ? @"" : bookItem.bookIconSmall) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.book4PhoneResource == nil ? @"" : bookItem.book4PhoneResource) UTF8String], -1, SQLITE_TRANSIENT);
            
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.book4PhoneResourceType == nil ? @"" : bookItem.book4PhoneResourceType) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.book4PadResource == nil ? @"" : bookItem.book4PadResource) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.book4PadResourceType == nil ? @"" : bookItem.book4PadResourceType) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookPrice == nil ? @"" : bookItem.bookPrice) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookPrice2 == nil ? @"" : bookItem.bookPrice2) UTF8String], -1, SQLITE_TRANSIENT);
            
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookAuthor == nil ? @"" : bookItem.bookAuthor) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookTranslator == nil ? @"" : bookItem.bookTranslator) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookCategory == nil ? @"" : bookItem.bookCategory) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookCategory1 == nil ? @"" : bookItem.bookCategory1) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookHot == nil ? @"" : bookItem.bookHot) UTF8String], -1, SQLITE_TRANSIENT);
            
            sqlite3_bind_int(statement, bindIndex++, [(bookItem.bookStar == nil ? @"0" : bookItem.bookStar) intValue]);
            sqlite3_bind_int(statement, bindIndex++, [(bookItem.bookDownCount == nil ? @"0" : bookItem.bookDownCount) intValue]);
            sqlite3_bind_int(statement, bindIndex++, [(bookItem.bookFocusCount == nil ? @"0" : bookItem.bookFocusCount) intValue]);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookApprovalTime == nil ? @"" : bookItem.bookApprovalTime) UTF8String], -1, SQLITE_TRANSIENT); 
            sqlite3_bind_int(statement, bindIndex++, [(bookItem.bookCommentCount == nil ? @"0" : bookItem.bookCommentCount) intValue]);
            
            success = sqlite3_step(statement);
            sqlite3_finalize(statement);
            
            if (success == SQLITE_ERROR) {
                NSLog(@"Error: failed to insert into the database with message.");
                return NO;
            } 
        }else{//如果存在图书在更新图书内容
            int success = sqlite3_prepare_v2(database_, sqlupdate, -1, &statement, NULL);
            if (success != SQLITE_OK) {
                NSLog(@"Error: failed to update: books");
                return NO;
            }
            
            int bindIndex = 1;
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookCode == nil ? @"" : bookItem.bookCode) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookName == nil ? @"" : bookItem.bookName) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookIconSmall == nil ? @"" : bookItem.bookIconSmall) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.book4PhoneResource == nil ? @"" : bookItem.book4PhoneResource) UTF8String], -1, SQLITE_TRANSIENT);
            
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.book4PhoneResourceType == nil ? @"" : bookItem.book4PhoneResourceType) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.book4PadResource == nil ? @"" : bookItem.book4PadResource) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.book4PadResourceType == nil ? @"" : bookItem.book4PadResourceType) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookPrice == nil ? @"" : bookItem.bookPrice) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookPrice2 == nil ? @"" : bookItem.bookPrice2) UTF8String], -1, SQLITE_TRANSIENT);
            
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookAuthor == nil ? @"" : bookItem.bookAuthor) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookTranslator == nil ? @"" : bookItem.bookTranslator) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookCategory == nil ? @"" : bookItem.bookCategory) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookCategory1 == nil ? @"" : bookItem.bookCategory1) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookHot == nil ? @"" : bookItem.bookHot) UTF8String], -1, SQLITE_TRANSIENT);
            
            sqlite3_bind_int(statement, bindIndex++, [(bookItem.bookStar == nil ? @"0" : bookItem.bookStar) intValue]);
            sqlite3_bind_int(statement, bindIndex++, [(bookItem.bookDownCount == nil ? @"0" : bookItem.bookDownCount) intValue]);
            sqlite3_bind_int(statement, bindIndex++, [(bookItem.bookFocusCount == nil ? @"0" : bookItem.bookFocusCount) intValue]);
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookApprovalTime == nil ? @"" : bookItem.bookApprovalTime) UTF8String], -1, SQLITE_TRANSIENT); 
            sqlite3_bind_int(statement, bindIndex++, [(bookItem.bookCommentCount == nil ? @"0" : bookItem.bookCommentCount) intValue]);
            
            sqlite3_bind_text(statement, bindIndex++, [(bookItem.bookId == nil ? @"" : bookItem.bookId) UTF8String], -1, SQLITE_TRANSIENT);
            
            success = sqlite3_step(statement);
            sqlite3_finalize(statement);
            
            if (success == SQLITE_ERROR) {
                NSLog(@"Error: failed to update books the database with message.");
                return NO;
            } 
        }
    }
    
    sqlite3_exec(database_, "commit transaction", NULL, NULL, &msg);
	return YES;
}

- (BOOL) insertAdData:(NSArray *)ads{
    char *msg;
    //NSData* ImageData = UIImagePNGRepresentation( channel.image_);
    //NSInteger Imagelen = [ImageData length];
    sqlite3_stmt *statement;
    static char *sql = "INSERT INTO cjp_ad (\
    ad_id, ad_title, ad_abstract, ad_content, ad_4_phone,\
    ad_4_pad, image_4_phone, image_4_pad, ad_state) \
    VALUES(?,?,?,?,?, ?,?,?,1)";
    
    sqlite3_exec(database_, "begin transaction", NULL, NULL, &msg);
    
    for (int i=0; i<[ads count]; i++) {
        int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);
        if (success != SQLITE_OK) {
            NSLog(@"Error: failed to insert: ads");
            return NO;
        }
        
        //NSLog(@"insert index:%d", i);
        AdItem *adItem = [ads objectAtIndex:i];
        
        sqlite3_bind_text(statement, 1, [adItem.adId UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement, 2, [adItem.adTitle UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement, 3, [adItem.adAbstract UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement, 4, [adItem.adContent UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement, 5, [adItem.ad4Phone UTF8String], -1, SQLITE_TRANSIENT);
        
        sqlite3_bind_text(statement, 6, [adItem.ad4Pad UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement, 7, [adItem.image4Phone UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement, 8, [adItem.image4Pad UTF8String], -1, SQLITE_TRANSIENT);
        
        success = sqlite3_step(statement);
        sqlite3_finalize(statement);
        
        if (success == SQLITE_ERROR) {
            NSLog(@"Error: failed to insert into the database with message.");
            return NO;
        } 
    }
    
    sqlite3_exec(database_, "commit transaction", NULL, NULL, &msg);
	return YES;
}

- (BOOL) insertSettingData{
    char *msg;
    sqlite3_stmt *statement;
    static char *sql = "INSERT INTO cjp_setting (\
    set_id, set_value, set_default) VALUES(?,?,?)";
    
    NSArray *settingData = [[NSArray alloc] initWithObjects:
                            [[NSArray alloc] initWithObjects: @"auto_remind", @"1", @"1", nil],
                            [[NSArray alloc] initWithObjects: @"auto_update", @"1", @"1", nil],
                            [[NSArray alloc] initWithObjects: @"disk_limit", @"500", @"500", nil],
                            [[NSArray alloc] initWithObjects: @"mybook_list", @"icon", @"icon", nil],
                            [[NSArray alloc] initWithObjects: @"mybook_bg", @"3", @"3", nil],
                            [[NSArray alloc] initWithObjects: @"book_right", @"1", @"1", nil],//book_right
                            nil ];
    
    sqlite3_exec(database_, "begin transaction", NULL, NULL, &msg);
    
    for (int i=0; i<[settingData count]; i++) {
        int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);
        if (success != SQLITE_OK) {
            NSLog(@"Error: failed to insert: setting");
            return NO;
        }
        
        NSArray *settingItem = [settingData objectAtIndex:i];
        
        sqlite3_bind_text(statement, 1, [[settingItem objectAtIndex:0] UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement, 2, [[settingItem objectAtIndex:1] UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement, 3, [[settingItem objectAtIndex:2] UTF8String], -1, SQLITE_TRANSIENT);
        
        success = sqlite3_step(statement);
        sqlite3_finalize(statement);
        
        if (success == SQLITE_ERROR) {
            NSLog(@"Error: failed to insert into the database with message.");
            return NO;
        } 
    }
    [settingData release];
    
    sqlite3_exec(database_, "commit transaction", NULL, NULL, &msg);
	return YES;
}

//更新图书的非关键数据
-(BOOL)updateBookData:(BookItem *)bookItem {
    char *msg;
    //NSLog(@"updateBookData");
    sqlite3_stmt *statement;
    static char *sql = "update cjp_book set \
    book_english_name = ?, \
    book_editor_info = ?,\
	book_content_info = ?,\
	book_author_info = ?,\
	book_catalog = ?,\
	book_free_content = ?,\
	book_icon = ?,\
    book_shot = ?,\
    book_series = ?,\
    book_price_print = ?,\
    book_language = ?,\
    book_issn = ?,\
    book_pages = ?,\
    book_words = ?,\
    book_score = ?, \
    book_press = ?, \
    book_press_time = ?, \
    book_ext_1 = '1', \
    book_mark = ? \
    where book_id = ?";
        
    sqlite3_exec(database_, "begin transaction", NULL, NULL, &msg);
    int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);
    if (success != SQLITE_OK) {
        NSLog(@"Error: failed to update: book extend data");
        return NO;
    }
    
    sqlite3_bind_text(statement, 1, [[bookItem bookEnglishName] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 2, [[bookItem bookEditorInfo] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 3, [[bookItem bookContentInfo] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 4, [[bookItem bookAuthorInfo] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 5, [[bookItem bookCatalog] UTF8String], -1, SQLITE_TRANSIENT);
    
    sqlite3_bind_text(statement, 6, [[bookItem bookFreeContent] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 7, [[bookItem bookIcon] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 8, [[bookItem bookShot] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 9, [[bookItem bookSeries] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 10, [[bookItem bookPricePrint] UTF8String], -1, SQLITE_TRANSIENT);
    
    sqlite3_bind_text(statement, 11, [[bookItem bookLanguage] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 12, [[bookItem bookIssn] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(statement, 13, [[bookItem bookPages] intValue]);
    sqlite3_bind_text(statement, 14, [[bookItem bookWords] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(statement, 15, [[bookItem bookScore] intValue]);
    
    sqlite3_bind_text(statement, 16, [[bookItem bookPress] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 17, [[bookItem bookPressTime] UTF8String], -1, SQLITE_TRANSIENT);
    if([bookItem bookMark] == nil){
        sqlite3_bind_text(statement, 18, [@"" UTF8String], -1, SQLITE_TRANSIENT);
    }else{
        sqlite3_bind_text(statement, 18, [[bookItem bookPressTime] UTF8String], -1, SQLITE_TRANSIENT);
    }
    sqlite3_bind_text(statement, 19, [[bookItem bookId] UTF8String], -1, SQLITE_TRANSIENT);
    
    success = sqlite3_step(statement);
    sqlite3_finalize(statement);
    
    if (success == SQLITE_ERROR) {
        NSLog(@"Error: failed to insert into the database with message.");
        return NO;
    } 
    sqlite3_exec(database_, "commit transaction", NULL, NULL, &msg);
    
	return YES;
}

-(BOOL)updateDownFocusData:(NSArray *)ads {
    char *msg;
    //NSLog(@"updateBookData");
    sqlite3_stmt *statement;
    static char *sql = "update cjp_book set book_down_count = ?, book_focus_count = ? where book_id = ?";
    
    sqlite3_exec(database_, "begin transaction", NULL, NULL, &msg);
    
    for (int i=0; i<[ads count]; i++) {
        int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);
        if (success != SQLITE_OK) {
            NSLog(@"Error: failed to insert: setting");
            return NO;
        }
        
        AdItem *adItem = [ads objectAtIndex:i];
        
        sqlite3_bind_int(statement, 1, [[adItem ext2] intValue]);
        sqlite3_bind_int(statement, 2, [[adItem ext3] intValue]);
        sqlite3_bind_text(statement, 3, [[adItem ext1] UTF8String], -1, SQLITE_TRANSIENT);
        
        success = sqlite3_step(statement);
        sqlite3_finalize(statement);
        
        if (success == SQLITE_ERROR) {
            NSLog(@"Error: failed to insert into the database with message.");
            return NO;
        } 
    }
    
    sqlite3_exec(database_, "commit transaction", NULL, NULL, &msg);
	return YES;
}

//查询数据库图书
- (BOOL)getData:(NSMutableArray *)books page:(NSString *)page keyword:(NSString *)key{
	sqlite3_stmt *statement = nil;
	static char *sql = "select book_id, book_code, book_name, book_icon_small, book_phone,\
    book_phone_type, book_pad, book_pad_type, book_price, book_price2, \
    book_author, book_translator, book_category1, book_hot, book_star, \
    book_down_count, book_focus_count, book_down_flag, book_buy_flag, book_icon_flag, \
    book_comment_count, pk_id \
    from cjp_book where book_price='0' and book_state = '1' and \
    (book_name like ? or book_author like ?) order by book_id desc limit ?, 21";
    //from cjp_book order by book_id desc limit ?, ?";
    
    if (sqlite3_prepare_v2(database_, sql, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement with message:get data.");
        return NO;
    }
    
    sqlite3_bind_text(statement, 1, [[NSString stringWithFormat:@"%%%@%%",key] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 2, [[NSString stringWithFormat:@"%%%@%%",key] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(statement, 3, ([page intValue] - 1) * 21);
//    sqlite3_bind_int(statement, 2, [page intValue] * 20);  
    
    while (sqlite3_step(statement) == SQLITE_ROW) {
        BookItem *bookItem = [[BookItem alloc] init];
        bookItem.bookId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        bookItem.bookCode = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        bookItem.bookName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
        bookItem.bookIconSmall = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
        bookItem.book4PhoneResource = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];
        
        bookItem.book4PhoneResourceType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 5)];
        bookItem.book4PadResource = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 6)];
        bookItem.book4PadResourceType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 7)];
        bookItem.bookPrice = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 8)];
        bookItem.bookPrice2 = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 9)];
        
        bookItem.bookAuthor = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 10)];
        bookItem.bookTranslator = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 11)];
        bookItem.bookCategory1 = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 12)];
        bookItem.bookHot = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 13)];
        bookItem.bookStar = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 14)];
        
        bookItem.bookDownCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 15)];
        bookItem.bookFocusCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 16)];
        if( (char *)sqlite3_column_text(statement, 17) != NULL ) {
            bookItem.bookDownFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 17)];
        }
        if( (char *)sqlite3_column_text(statement, 18) != NULL ) {
            bookItem.bookBuyFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 18)];
        }
        if( (char *)sqlite3_column_text(statement, 19) != NULL ) {
            bookItem.bookIconFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 19)];
        }
        if( (char *)sqlite3_column_text(statement, 20) != NULL ) {
            bookItem.bookCommentCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 20)];
        }
        if( (char *)sqlite3_column_text(statement, 21) != NULL ) {
            bookItem.pkId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 21)];
        }
        
        //NSLog(@"DBUtil:getData:item:%@=%@", bookItem.bookCode, bookItem.bookName);
        [books addObject:bookItem];
        [bookItem release];
    }
    //NSLog(@"DBUtil:getData:%d", [books count]);
    sqlite3_finalize(statement);
    
	return YES;
}

//查询数据库推荐图书
- (BOOL)getHotData:(NSMutableArray *)books page:(NSString *)page keyword:(NSString *)key {
	sqlite3_stmt *statement = nil;
	static char *sql = "select book_id, book_code, book_name, book_icon_small, book_phone,\
    book_phone_type, book_pad, book_pad_type, book_price, book_price2, \
    book_author, book_translator, book_category1, book_hot, book_star, \
    book_down_count, book_focus_count, book_down_flag, book_buy_flag, book_icon_flag, \
    book_comment_count, pk_id \
    from cjp_book where book_price='0' and book_state = '1' and \
    book_hot = '1' and (book_name like ? or book_author like ?) \
    order by book_id desc";// limit ?, 21";
    //order by book_id desc limit ?, ?";
    
    if (sqlite3_prepare_v2(database_, sql, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement with message:get data.");
        return NO;
    }
    
    sqlite3_bind_text(statement, 1, [[NSString stringWithFormat:@"%%%@%%",key] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 2, [[NSString stringWithFormat:@"%%%@%%",key] UTF8String], -1, SQLITE_TRANSIENT);
//    sqlite3_bind_int(statement, 3, ([page intValue] - 1) * 21);
//    sqlite3_bind_int(statement, 1, ([page intValue] - 1) * 20);
//    sqlite3_bind_int(statement, 2, [page intValue] * 20);  
    
    while (sqlite3_step(statement) == SQLITE_ROW) {
        BookItem *bookItem = [[BookItem alloc] init];
        bookItem.bookId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        bookItem.bookCode = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        bookItem.bookName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
        bookItem.bookIconSmall = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
        bookItem.book4PhoneResource = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];
        
        bookItem.book4PhoneResourceType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 5)];
        bookItem.book4PadResource = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 6)];
        bookItem.book4PadResourceType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 7)];
        bookItem.bookPrice = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 8)];
        bookItem.bookPrice2 = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 9)];
        
        bookItem.bookAuthor = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 10)];
        bookItem.bookTranslator = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 11)];
        bookItem.bookCategory1 = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 12)];
        bookItem.bookHot = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 13)];
        bookItem.bookStar = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 14)];
        
        bookItem.bookDownCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 15)];
        bookItem.bookFocusCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 16)];
        if( (char *)sqlite3_column_text(statement, 17) != NULL ) {
            bookItem.bookDownFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 17)];
        }
        if( (char *)sqlite3_column_text(statement, 18) != NULL ) {
            bookItem.bookBuyFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 18)];
        }
        if( (char *)sqlite3_column_text(statement, 19) != NULL ) {
            bookItem.bookIconFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 19)];
        }
        if( (char *)sqlite3_column_text(statement, 20) != NULL ) {
            bookItem.bookCommentCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 20)];
        }
        if( (char *)sqlite3_column_text(statement, 21) != NULL ) {
            bookItem.pkId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 21)];
        }
        //NSLog(@"DBUtil:getData:item:%@=%@", bookItem.bookCode, bookItem.bookName);
        [books addObject:bookItem];
        [bookItem release];
    }
    //NSLog(@"DBUtil:getData:%d", [books count]);
    sqlite3_finalize(statement);
    
	return YES;
}


//查询数据库推荐图书
- (BOOL)getTopData:(NSMutableArray *)books page:(NSString *)page keyword:(NSString *)key {
    if(![page isEqualToString:@"1"]){
        return YES;
    }
    
	sqlite3_stmt *statement = nil;
	static char *sql = "select book_id, book_code, book_name, book_icon_small, book_phone,\
    book_phone_type, book_pad, book_pad_type, book_price, book_price2, \
    book_author, book_translator, book_category1, book_hot, book_star, \
    book_down_count, book_focus_count, book_down_flag, book_buy_flag, book_icon_flag, \
    book_comment_count, pk_id \
    from cjp_book where book_price='0' and book_state = '1' and \
    (book_name like ? or book_author like ?) \
    order by book_down_count desc, book_focus_count desc limit 21";
    //from cjp_book order by book_down_count desc limit ?, ?";
    
    if (sqlite3_prepare_v2(database_, sql, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement with message:get data.");
        return NO;
    }
    
    sqlite3_bind_text(statement, 1, [[NSString stringWithFormat:@"%%%@%%",key] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 2, [[NSString stringWithFormat:@"%%%@%%",key] UTF8String], -1, SQLITE_TRANSIENT);
//    sqlite3_bind_int(statement, 1, ([page intValue] - 1) * 20);
//    sqlite3_bind_int(statement, 2, [page intValue] * 20);  
    
    while (sqlite3_step(statement) == SQLITE_ROW) {
        BookItem *bookItem = [[BookItem alloc] init];
        bookItem.bookId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        bookItem.bookCode = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        bookItem.bookName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
        bookItem.bookIconSmall = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
        bookItem.book4PhoneResource = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];
        
        bookItem.book4PhoneResourceType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 5)];
        bookItem.book4PadResource = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 6)];
        bookItem.book4PadResourceType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 7)];
        bookItem.bookPrice = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 8)];
        bookItem.bookPrice2 = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 9)];
        
        bookItem.bookAuthor = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 10)];
        bookItem.bookTranslator = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 11)];
        bookItem.bookCategory1 = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 12)];
        bookItem.bookHot = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 13)];
        bookItem.bookStar = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 14)];
        
        bookItem.bookDownCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 15)];
        bookItem.bookFocusCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 16)];
        if( (char *)sqlite3_column_text(statement, 17) != NULL ) {
            bookItem.bookDownFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 17)];
        }
        if( (char *)sqlite3_column_text(statement, 18) != NULL ) {
            bookItem.bookBuyFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 18)];
        }
        if( (char *)sqlite3_column_text(statement, 19) != NULL ) {
            bookItem.bookIconFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 19)];
        }
        if( (char *)sqlite3_column_text(statement, 20) != NULL ) {
            bookItem.bookCommentCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 20)];
        }
        if( (char *)sqlite3_column_text(statement, 21) != NULL ) {
            bookItem.pkId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 21)];
        }
        //NSLog(@"DBUtil:getData:item:%@=%@", bookItem.bookCode, bookItem.bookName);
        [books addObject:bookItem];
        [bookItem release];
    }
    //NSLog(@"DBUtil:getData:%d", [books count]);
    sqlite3_finalize(statement);
    
	return YES;
}


//查询数据库推荐图书
- (BOOL)getTopNewData:(NSMutableArray *)books page:(NSString *)page keyword:(NSString *)key {
    if(![page isEqualToString:@"1"]){
        return YES;
    }
    
	sqlite3_stmt *statement = nil;
	static char *sql = "select book_id, book_code, book_name, book_icon_small, book_phone,\
    book_phone_type, book_pad, book_pad_type, book_price, book_price2, \
    book_author, book_translator, book_category1, book_hot, book_star, \
    book_down_count, book_focus_count, book_down_flag, book_buy_flag, book_icon_flag, \
    book_comment_count, pk_id \
    from cjp_book where book_price='0' and book_state = '1' and \
    (book_name like ? or book_author like ?) \
    order by book_approval_time desc limit 21";
    //from cjp_book order by book_down_count desc limit ?, ?";
    
    if (sqlite3_prepare_v2(database_, sql, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement with message:get data.");
        return NO;
    }
    
    sqlite3_bind_text(statement, 1, [[NSString stringWithFormat:@"%%%@%%",key] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 2, [[NSString stringWithFormat:@"%%%@%%",key] UTF8String], -1, SQLITE_TRANSIENT);
    //    sqlite3_bind_int(statement, 1, ([page intValue] - 1) * 20);
    //    sqlite3_bind_int(statement, 2, [page intValue] * 20);  
    
    while (sqlite3_step(statement) == SQLITE_ROW) {
        BookItem *bookItem = [[BookItem alloc] init];
        bookItem.bookId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        bookItem.bookCode = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        bookItem.bookName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
        bookItem.bookIconSmall = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
        bookItem.book4PhoneResource = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];
        
        bookItem.book4PhoneResourceType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 5)];
        bookItem.book4PadResource = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 6)];
        bookItem.book4PadResourceType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 7)];
        bookItem.bookPrice = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 8)];
        bookItem.bookPrice2 = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 9)];
        
        bookItem.bookAuthor = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 10)];
        bookItem.bookTranslator = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 11)];
        bookItem.bookCategory1 = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 12)];
        bookItem.bookHot = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 13)];
        bookItem.bookStar = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 14)];
        
        bookItem.bookDownCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 15)];
        bookItem.bookFocusCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 16)];
        if( (char *)sqlite3_column_text(statement, 17) != NULL ) {
            bookItem.bookDownFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 17)];
        }
        if( (char *)sqlite3_column_text(statement, 18) != NULL ) {
            bookItem.bookBuyFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 18)];
        }
        if( (char *)sqlite3_column_text(statement, 19) != NULL ) {
            bookItem.bookIconFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 19)];
        }
        if( (char *)sqlite3_column_text(statement, 20) != NULL ) {
            bookItem.bookCommentCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 20)];
        }
        if( (char *)sqlite3_column_text(statement, 21) != NULL ) {
            bookItem.pkId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 21)];
        }
        //NSLog(@"DBUtil:getData:item:%@=%@", bookItem.bookCode, bookItem.bookName);
        [books addObject:bookItem];
        [bookItem release];
    }
    //NSLog(@"DBUtil:getData:%d", [books count]);
    sqlite3_finalize(statement);
    
	return YES;
}

//查询数据库推荐图书
- (BOOL)getFreeData:(NSMutableArray *)books page:(NSString *)page keyword:(NSString *)key {
	sqlite3_stmt *statement = nil;
	static char *sql = "select book_id, book_code, book_name, book_icon_small, book_phone,\
    book_phone_type, book_pad, book_pad_type, book_price, book_price2, \
    book_author, book_translator, book_category1, book_hot, book_star, \
    book_down_count, book_focus_count, book_down_flag, book_buy_flag, book_icon_flag, \
    book_comment_count, pk_id \
    from cjp_book where book_price='0' and book_state = '1' and \
    (book_price = '0' or book_price2 = '0') \
    and (book_name like ? or book_author like ?) \
    order by book_id desc limit ?, 21";
    //order by book_id desc limit ?, ?";
    
    if (sqlite3_prepare_v2(database_, sql, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement with message:get data.");
        return NO;
    }
    
    sqlite3_bind_text(statement, 1, [[NSString stringWithFormat:@"%%%@%%",key] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 2, [[NSString stringWithFormat:@"%%%@%%",key] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(statement, 3, ([page intValue] - 1) * 21);
//    sqlite3_bind_int(statement, 1, ([page intValue] - 1) * 20);
//    sqlite3_bind_int(statement, 2, [page intValue] * 20);  
    
    while (sqlite3_step(statement) == SQLITE_ROW) {
        BookItem *bookItem = [[BookItem alloc] init];
        bookItem.bookId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        bookItem.bookCode = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        bookItem.bookName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
        bookItem.bookIconSmall = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
        bookItem.book4PhoneResource = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];
        
        bookItem.book4PhoneResourceType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 5)];
        bookItem.book4PadResource = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 6)];
        bookItem.book4PadResourceType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 7)];
        bookItem.bookPrice = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 8)];
        bookItem.bookPrice2 = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 9)];
        
        bookItem.bookAuthor = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 10)];
        bookItem.bookTranslator = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 11)];
        bookItem.bookCategory1 = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 12)];
        bookItem.bookHot = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 13)];
        bookItem.bookStar = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 14)];
        
        bookItem.bookDownCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 15)];
        bookItem.bookFocusCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 16)];
        if( (char *)sqlite3_column_text(statement, 17) != NULL ) {
            bookItem.bookDownFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 17)];
        }
        if( (char *)sqlite3_column_text(statement, 18) != NULL ) {
            bookItem.bookBuyFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 18)];
        }
        if( (char *)sqlite3_column_text(statement, 19) != NULL ) {
            bookItem.bookIconFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 19)];
        }
        if( (char *)sqlite3_column_text(statement, 20) != NULL ) {
            bookItem.bookCommentCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 20)];
        }
        if( (char *)sqlite3_column_text(statement, 21) != NULL ) {
            bookItem.pkId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 21)];
        }
        
        //NSLog(@"DBUtil:getData:item:%@=%@", bookItem.bookCode, bookItem.bookName);
        [books addObject:bookItem];
        [bookItem release];
    }
    //NSLog(@"DBUtil:getData:%d", [books count]);
    sqlite3_finalize(statement);
    
	return YES;
}

//查询数据库推荐图书
- (BOOL)getFavoriteData:(NSMutableArray *)books page:(NSString *)page keyword:(NSString *)key {
	sqlite3_stmt *statement = nil;
	static char *sql = "select book_id, book_code, book_name, book_icon_small, book_phone,\
    book_phone_type, book_pad, book_pad_type, book_price, book_price2, \
    book_author, book_translator, book_category1, book_hot, book_star, \
    book_down_count, book_focus_count, book_down_flag, book_buy_flag, book_icon_flag, \
    book_comment_count, pk_id \
    from cjp_book where book_price='0' and book_state = '1' and \
    (book_favorite is not null and book_favorite = '1') \
    and (book_name like ? or book_author like ?) \
    order by book_id desc limit ?, 21";
    //order by book_id desc limit ?, ?";
    
    if (sqlite3_prepare_v2(database_, sql, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement with message:get data.");
        return NO;
    }
    
    sqlite3_bind_text(statement, 1, [[NSString stringWithFormat:@"%%%@%%",key] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 2, [[NSString stringWithFormat:@"%%%@%%",key] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(statement, 3, ([page intValue] - 1) * 21);
//    sqlite3_bind_int(statement, 1, ([page intValue] - 1) * 20);
//    sqlite3_bind_int(statement, 2, [page intValue] * 20);  
    
    while (sqlite3_step(statement) == SQLITE_ROW) {
        BookItem *bookItem = [[BookItem alloc] init];
        bookItem.bookId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        bookItem.bookCode = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        bookItem.bookName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
        bookItem.bookIconSmall = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
        bookItem.book4PhoneResource = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];
        
        bookItem.book4PhoneResourceType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 5)];
        bookItem.book4PadResource = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 6)];
        bookItem.book4PadResourceType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 7)];
        bookItem.bookPrice = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 8)];
        bookItem.bookPrice2 = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 9)];
        
        bookItem.bookAuthor = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 10)];
        bookItem.bookTranslator = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 11)];
        bookItem.bookCategory1 = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 12)];
        bookItem.bookHot = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 13)];
        bookItem.bookStar = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 14)];
        
        bookItem.bookDownCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 15)];
        bookItem.bookFocusCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 16)];
        if( (char *)sqlite3_column_text(statement, 17) != NULL ) {
            bookItem.bookDownFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 17)];
        }
        if( (char *)sqlite3_column_text(statement, 18) != NULL ) {
            bookItem.bookBuyFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 18)];
        }
        if( (char *)sqlite3_column_text(statement, 19) != NULL ) {
            bookItem.bookIconFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 19)];
        }
        if( (char *)sqlite3_column_text(statement, 20) != NULL ) {
            bookItem.bookCommentCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 20)];
        }
        if( (char *)sqlite3_column_text(statement, 21) != NULL ) {
            bookItem.pkId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 21)];
        }
        
        //NSLog(@"DBUtil:getData:item:%@=%@", bookItem.bookCode, bookItem.bookName);
        [books addObject:bookItem];
        [bookItem release];
    }
    //NSLog(@"DBUtil:getData:%d", [books count]);
    sqlite3_finalize(statement);
    
	return YES;
}

//查询数据库搜索图书
- (BOOL)getSearchData:(NSMutableArray *)books page:(NSString *)page keyword:(NSString *)key {
	sqlite3_stmt *statement = nil;
	static char *sql = "select book_id, book_code, book_name, book_icon_small, book_phone,\
    book_phone_type, book_pad, book_pad_type, book_price, book_price2, \
    book_author, book_translator, book_category1, book_hot, book_star, \
    book_down_count, book_focus_count, book_down_flag, book_buy_flag, book_icon_flag, \
    book_comment_count, pk_id \
    from cjp_book where book_price='0' and book_state = '1' and \
    (book_name like ? or book_author like ?) \
    order by book_id desc";// limit ?, 21";
    //order by book_id desc limit ?, ?";
    
    if (sqlite3_prepare_v2(database_, sql, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement with message:get data.");
        return NO;
    }
    
    sqlite3_bind_text(statement, 1, [[NSString stringWithFormat:@"%%%@%%",key] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 2, [[NSString stringWithFormat:@"%%%@%%",key] UTF8String], -1, SQLITE_TRANSIENT);
//    sqlite3_bind_int(statement, 3, ([page intValue] - 1) * 21);
    //    sqlite3_bind_int(statement, 1, ([page intValue] - 1) * 20);
    //    sqlite3_bind_int(statement, 2, [page intValue] * 20);  
    
    while (sqlite3_step(statement) == SQLITE_ROW) {
        BookItem *bookItem = [[BookItem alloc] init];
        bookItem.bookId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        bookItem.bookCode = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        bookItem.bookName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
        bookItem.bookIconSmall = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
        bookItem.book4PhoneResource = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];
        
        bookItem.book4PhoneResourceType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 5)];
        bookItem.book4PadResource = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 6)];
        bookItem.book4PadResourceType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 7)];
        bookItem.bookPrice = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 8)];
        bookItem.bookPrice2 = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 9)];
        
        bookItem.bookAuthor = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 10)];
        bookItem.bookTranslator = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 11)];
        bookItem.bookCategory1 = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 12)];
        bookItem.bookHot = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 13)];
        bookItem.bookStar = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 14)];
        
        bookItem.bookDownCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 15)];
        bookItem.bookFocusCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 16)];
        if( (char *)sqlite3_column_text(statement, 17) != NULL ) {
            bookItem.bookDownFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 17)];
        }
        if( (char *)sqlite3_column_text(statement, 18) != NULL ) {
            bookItem.bookBuyFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 18)];
        }
        if( (char *)sqlite3_column_text(statement, 19) != NULL ) {
            bookItem.bookIconFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 19)];
        }
        if( (char *)sqlite3_column_text(statement, 20) != NULL ) {
            bookItem.bookCommentCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 20)];
        }
        if( (char *)sqlite3_column_text(statement, 21) != NULL ) {
            bookItem.pkId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 21)];
        }
        
        //NSLog(@"DBUtil:getData:item:%@=%@", bookItem.bookCode, bookItem.bookName);
        [books addObject:bookItem];
        [bookItem release];
    }
    //NSLog(@"DBUtil:getData:%d", [books count]);
    sqlite3_finalize(statement);
    
	return YES;
}


//查询数据库购买图书
- (BOOL)getMyData:(NSMutableArray *)books page:(NSString *)page keyword:(NSString *)key {
	sqlite3_stmt *statement = nil;
	static char *sql = "select book_id, book_code, book_name, book_icon_small, book_phone,\
    book_phone_type, book_pad, book_pad_type, book_price, book_price2, \
    book_author, book_translator, book_category1, book_hot, book_star, \
    book_down_count, book_focus_count, book_down_flag, book_buy_flag, book_icon_flag, \
    book_comment_count, pk_id \
    from cjp_book where book_price='0' and book_state = '1' and \
    (book_buy_flag is not null and book_buy_flag = '1') \
    and (book_down_flag is not null or book_down_flag = '1') \
    and (book_name like ? or book_author like ?) \
    order by book_id desc";
    //order by book_id desc limit ?, ?";
    
    if (sqlite3_prepare_v2(database_, sql, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement with message:get data.");
        return NO;
    }
    
    sqlite3_bind_text(statement, 1, [[NSString stringWithFormat:@"%%%@%%",key] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 2, [[NSString stringWithFormat:@"%%%@%%",key] UTF8String], -1, SQLITE_TRANSIENT);
//    sqlite3_bind_int(statement, 1, ([page intValue] - 1) * 20);
//    sqlite3_bind_int(statement, 2, [page intValue] * 20);  
    
    while (sqlite3_step(statement) == SQLITE_ROW) {
        BookItem *bookItem = [[BookItem alloc] init];
        bookItem.bookId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        bookItem.bookCode = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        bookItem.bookName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
        bookItem.bookIconSmall = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
        bookItem.book4PhoneResource = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];
        
        bookItem.book4PhoneResourceType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 5)];
        bookItem.book4PadResource = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 6)];
        bookItem.book4PadResourceType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 7)];
        bookItem.bookPrice = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 8)];
        bookItem.bookPrice2 = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 9)];
        
        bookItem.bookAuthor = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 10)];
        bookItem.bookTranslator = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 11)];
        bookItem.bookCategory1 = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 12)];
        bookItem.bookHot = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 13)];
        bookItem.bookStar = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 14)];
        
        bookItem.bookDownCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 15)];
        bookItem.bookFocusCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 16)];
        if( (char *)sqlite3_column_text(statement, 17) != NULL ) {
            bookItem.bookDownFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 17)];
        }
        if( (char *)sqlite3_column_text(statement, 18) != NULL ) {
            bookItem.bookBuyFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 18)];
        }
        if( (char *)sqlite3_column_text(statement, 19) != NULL ) {
            bookItem.bookIconFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 19)];
        }
        if( (char *)sqlite3_column_text(statement, 20) != NULL ) {
            bookItem.bookCommentCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 20)];
        }
        if( (char *)sqlite3_column_text(statement, 21) != NULL ) {
            bookItem.pkId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 21)];
        }
        
        //NSLog(@"DBUtil:getData:item:%@=%@", bookItem.bookCode, bookItem.bookName);
        [books addObject:bookItem];
        [bookItem release];
    }
    //NSLog(@"DBUtil:getData:%d", [books count]);
    sqlite3_finalize(statement);
    
	return YES;
}

-(BOOL)getBookDetailData:(BookItem *)bookDetail bookId:(NSString *)bookId {
    sqlite3_stmt *statement = nil;
	static char *sql = "select book_id, book_code, book_name, book_icon_small, book_phone,\
    book_phone_type, book_pad, book_pad_type, book_price, book_price2, \
    book_author, book_translator, book_category1, book_hot, book_star, \
    book_down_count, book_focus_count, book_down_flag, book_buy_flag, book_icon_flag, \
    book_comment_count, book_content_info, book_editor_info, book_author_info, book_catalog, \
    book_series, book_ext_1, book_mark, pk_id, book_language, book_words, book_press, book_press_time \
    from cjp_book \
    where  book_state = '1' and book_id = ?";
    
    if (sqlite3_prepare_v2(database_, sql, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement with message:get data.");
        return NO;
    }
    
    sqlite3_bind_text(statement, 1, [bookId UTF8String], -1, SQLITE_TRANSIENT);
    
    if (sqlite3_step(statement) == SQLITE_ROW) {
        bookDetail.bookId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        bookDetail.bookCode = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        bookDetail.bookName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
        bookDetail.bookIconSmall = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
        bookDetail.book4PhoneResource = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];
        
        bookDetail.book4PhoneResourceType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 5)];
        bookDetail.book4PadResource = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 6)];
        bookDetail.book4PadResourceType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 7)];
        bookDetail.bookPrice = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 8)];
        bookDetail.bookPrice2 = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 9)];
        
        bookDetail.bookAuthor = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 10)];
        bookDetail.bookTranslator = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 11)];
        bookDetail.bookCategory1 = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 12)];
        bookDetail.bookHot = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 13)];
        bookDetail.bookStar = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 14)];
        
        bookDetail.bookDownCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 15)];
        bookDetail.bookFocusCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 16)];
        if( (char *)sqlite3_column_text(statement, 17) != NULL ) {
            bookDetail.bookDownFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 17)];
        }
        if( (char *)sqlite3_column_text(statement, 18) != NULL ) {
            bookDetail.bookBuyFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 18)];
        }
        if( (char *)sqlite3_column_text(statement, 19) != NULL ) {
            bookDetail.bookIconFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 19)];
        }
        
        if( (char *)sqlite3_column_text(statement, 20) != NULL ) {
            bookDetail.bookCommentCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 20)];
        }
        //book_content_info, book_editor_info, book_author_info, book_catalog
        if( (char *)sqlite3_column_text(statement, 21) != NULL ) {
            bookDetail.bookContentInfo = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 21)];
        }
        if( (char *)sqlite3_column_text(statement, 22) != NULL ) {
            bookDetail.bookEditorInfo = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 22)];
        }
        if( (char *)sqlite3_column_text(statement, 23) != NULL ) {
            bookDetail.bookAuthorInfo = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 23)];
        }
        if( (char *)sqlite3_column_text(statement, 24) != NULL ) {
            bookDetail.bookCatalog = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 24)];
        }
        
        if( (char *)sqlite3_column_text(statement, 25) != NULL ) {
            bookDetail.bookSeries = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 25)];
        }
        if( (char *)sqlite3_column_text(statement, 26) != NULL ) {
            bookDetail.bookExt1 = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 26)];
        }
        if( (char *)sqlite3_column_text(statement, 27) != NULL ) {
            bookDetail.bookMark = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 27)];
        }
        if( (char *)sqlite3_column_text(statement, 28) != NULL ) {
            bookDetail.pkId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 28)];
        }
        if( (char *)sqlite3_column_text(statement, 29) != NULL ) {
            bookDetail.bookLanguage = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 29)];
        }
        if( (char *)sqlite3_column_text(statement, 30) != NULL ) {
            bookDetail.bookWords = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 30)];
        }
        if( (char *)sqlite3_column_text(statement, 31) != NULL ) {
            bookDetail.bookPress = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 31)];
        }
        if( (char *)sqlite3_column_text(statement, 32) != NULL ) {
            bookDetail.bookPressTime = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 32)];
        }
    }
    //NSLog(@"DBUtil:getData:%d", [books count]);
    sqlite3_finalize(statement);
    
	return YES;
}

//查询数据库栏目图书
- (BOOL)getCategoryData:(NSMutableArray *)books categoryName:(NSString *)category page:(NSString *)page keyword:(NSString *)key {
	sqlite3_stmt *statement = nil;
	static char *sql = "select book_id, book_code, book_name, book_icon_small,book_phone,\
    book_phone_type, book_pad, book_pad_type, book_price, book_price2, \
    book_author, book_translator, book_category1, book_hot, book_star, \
    book_down_count, book_focus_count, book_down_flag, book_buy_flag, book_icon_flag, \
    book_category, book_comment_count, pk_id from cjp_book where book_category1 = ? \
    and book_price='0' and book_state = '1' and \
    (book_name like ? or book_author like ?) \
    order by book_id desc limit ?, 21";
    //order by book_id desc limit ?, ?";
    
    if (sqlite3_prepare_v2(database_, sql, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement with message:get data.");
        return NO;
    }
    sqlite3_bind_text(statement, 1, [category UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 2, [[NSString stringWithFormat:@"%%%@%%",key] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 3, [[NSString stringWithFormat:@"%%%@%%",key] UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(statement, 4, ([page intValue] - 1) * 21);
//    sqlite3_bind_int(statement, 3, [page intValue] * 20);  
    
    while (sqlite3_step(statement) == SQLITE_ROW) {
        BookItem *bookItem = [[BookItem alloc] init];
        bookItem.bookId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        bookItem.bookCode = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        bookItem.bookName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
        bookItem.bookIconSmall = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
        bookItem.book4PhoneResource = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];
        
        bookItem.book4PhoneResourceType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 5)];
        bookItem.book4PadResource = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 6)];
        bookItem.book4PadResourceType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 7)];
        bookItem.bookPrice = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 8)];
        bookItem.bookPrice2 = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 9)];
        
        bookItem.bookAuthor = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 10)];
        bookItem.bookTranslator = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 11)];
        bookItem.bookCategory1 = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 12)];
        bookItem.bookHot = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 13)];
        bookItem.bookStar = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 14)];
        
        bookItem.bookDownCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 15)];
        bookItem.bookFocusCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 16)];
        if( (char *)sqlite3_column_text(statement, 17) != NULL ) {
            bookItem.bookDownFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 17)];
        }
        if( (char *)sqlite3_column_text(statement, 18) != NULL ) {
            bookItem.bookBuyFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 18)];
        }
        if( (char *)sqlite3_column_text(statement, 19) != NULL ) {
            bookItem.bookIconFlag = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 19)];
        }
        bookItem.bookCategory = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 20)];
        
        if( (char *)sqlite3_column_text(statement, 21) != NULL ) {
            bookItem.bookCommentCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 21)];
        }
        if( (char *)sqlite3_column_text(statement, 22) != NULL ) {
            bookItem.pkId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 22)];
        }
        
        [books addObject:bookItem];
        [bookItem release];
    }
    sqlite3_finalize(statement);
    
	return YES;
}

//获取最后的审批时间
-(NSString *)getLastApprovalTime {
    NSString *lastApproval = @"";
    
    sqlite3_stmt *statement = nil;
	static char *sql = "select book_approval_time from cjp_book \
    where book_approval_time is not null order by book_approval_time desc limit 1";
    
    if (sqlite3_prepare_v2(database_, sql, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement with message:get data.");
        return NO;
    }
    
    while (sqlite3_step(statement) == SQLITE_ROW) {
        lastApproval = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
    }
    
    sqlite3_finalize(statement);
    
	return lastApproval;
}

//获取分类
-(BOOL)getCategories:(NSMutableArray *)categories {
    sqlite3_stmt *statement = nil;
	static char *sql = "SELECT book_category1, count(*) FROM cjp_book where book_price='0' and book_state = '1' group by book_category1";
    
    if (sqlite3_prepare_v2(database_, sql, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement with message:get categories.");
        return NO;
    }
    
    while (sqlite3_step(statement) == SQLITE_ROW) {
        CategoryCount *categoryCount = [[CategoryCount alloc] init];
        categoryCount.categoryName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        categoryCount.categoryCount = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)]; 
        [categories addObject:categoryCount];
        [categoryCount release];
    }
    //NSLog(@"DBUtil:getCategories:%d", [categories count]);
    sqlite3_finalize(statement);
    
	return YES;
}

//更新
//columnField=buy value=1 : 更新购买（book_buy_flag=1）
//columnField=down value=1 : 更新购买（book_buy_flag=1, book_down_flag = 1）
//columnField=favorite value=1 : 更新收藏（book_favorite=1）
//columnField=delete value=1 : 更新删除（book_delete=1,book_buy_flag='-1'）
//columnField=mark value=1,3,5 : 更新书签（book_mark=value）
//columnField=icon value=1 : 更新下载图片（book_icon_flag=value）
-(BOOL)updateBook:(NSString *)columnField key:(NSString *)key value:(NSString *)value {
    char *msg;
    //NSLog(@"updateBook");
    sqlite3_stmt *statement;
    NSString *sql = nil;
    if([columnField isEqualToString:@"buy"]){
        sql = @"update cjp_book set book_buy_flag = '1' where book_id = ?";
    }else if([columnField isEqualToString:@"down"]){
        sql = @"update cjp_book set book_buy_flag = '1', book_down_flag = '1' where book_id = ?";
    }else if([columnField isEqualToString:@"favorite"]){
        sql = @"update cjp_book set book_favorite = '1' where book_id = ?";
    }else if([columnField isEqualToString:@"icon"]){
        sql = @"update cjp_book set book_icon_flag = '1' where book_id = ?";
    }else if([columnField isEqualToString:@"delete"]){
        sql = @"update cjp_book set book_delete = '1',book_buy_flag='-1' where book_id = ?";
    }else if([columnField isEqualToString:@"mark"]){
        sql = [NSString stringWithFormat:
                           @"update cjp_book set book_mark = '%@' where book_id = ?", value];
    }else if([columnField isEqualToString:@"appleid"]){
        sql = @"update cjp_book set book_buy_flag = '1' where book_code = ?";
    }
    
    if(![sql isEqualToString:@""]){
    
        sqlite3_exec(database_, "begin transaction", NULL, NULL, &msg);
        int success = sqlite3_prepare_v2(database_, [sql UTF8String], -1, &statement, NULL);
        if (success != SQLITE_OK) {
            NSLog(@"Error: failed to update: book");
            return NO;
        }
        sqlite3_bind_text(statement, 1, [key UTF8String], -1, SQLITE_TRANSIENT);
        success = sqlite3_step(statement);
        sqlite3_finalize(statement);
        
        if (success == SQLITE_ERROR) {
            NSLog(@"Error: failed to insert into the database with message.");
            return NO;
        } 
        sqlite3_exec(database_, "commit transaction", NULL, NULL, &msg);
    }
	return YES;
}

//批量删除图书
-(BOOL)batchDeleteBook:(NSMutableArray *)bookIds {
    if(bookIds == nil){
        return NO;
    }
    
    char *msg;
    sqlite3_stmt *statement;
    static char *sql = "update cjp_book set book_down_flag = NULL where book_id = ?";
    
    sqlite3_exec(database_, "begin transaction", NULL, NULL, &msg);
    
    for (int i=0; i<[bookIds count]; i++) {
        int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);
        if (success != SQLITE_OK) {
            NSLog(@"Error: failed to delete: books");
            return NO;
        }
        
        sqlite3_bind_text(statement, 1, [[bookIds objectAtIndex:i] UTF8String], -1, SQLITE_TRANSIENT);
                success = sqlite3_step(statement);
        sqlite3_finalize(statement);
        
        if (success == SQLITE_ERROR) {
            NSLog(@"Error: failed to delete cjp_book(%@) with message.", [bookIds objectAtIndex:i]);
            return NO;
        } 
    }
    
    sqlite3_exec(database_, "commit transaction", NULL, NULL, &msg);
	return YES;
}

-(BOOL)getTxtPages:(NSMutableArray *)pages page:(NSString *)bookId orient:(NSString *)orient fontSize:(NSString *)fontSize {
    sqlite3_stmt *statement = nil;
	static char *sql = "select range_location, range_length from cjp_txtpages \
    where book_id = ? and book_orient = ? and font_size = ? ";
    //from cjp_book order by book_id desc limit ?, ?";
    
    if (sqlite3_prepare_v2(database_, sql, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement with message:get data.");
        return NO;
    }
    
    sqlite3_bind_text(statement, 1, [bookId UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 2, [orient UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(statement, 3, [fontSize intValue]);
    
    while (sqlite3_step(statement) == SQLITE_ROW) {
        NSRange range = NSMakeRange(0, 0);
        range.location = sqlite3_column_int(statement, 0);
        range.length = sqlite3_column_int(statement, 1);
        
        [pages addObject:[NSValue valueWithRange:range]];
    }
    sqlite3_finalize(statement);
    
	return YES;
}

-(BOOL)setTxtPages:(NSMutableArray *)pages page:(NSString *)bookId orient:(NSString *)orient fontSize:(NSString *)fontSize {
    char *msg;
    sqlite3_stmt *statement;
    static char *sql = "INSERT INTO cjp_txtpages (\
    book_id, book_orient, font_size, range_location, range_length) \
    VALUES(?,?,?,?,?)";
    
    sqlite3_exec(database_, "begin transaction", NULL, NULL, &msg);

    for (int i=0; i<[pages count]; i++) {
        int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);
        if (success != SQLITE_OK) {
            NSLog(@"Error: failed to insert: books");
            return NO;
        }
        
        //NSLog(@"insert index:%d", i);
        NSRange range;
        NSValue *value = [pages objectAtIndex:i];
        [value getValue:&range];
        
        sqlite3_bind_text(statement, 1, [bookId UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement, 2, [orient UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_int(statement, 3, [fontSize intValue]);
        sqlite3_bind_int(statement, 4, range.location);
        sqlite3_bind_int(statement, 5, range.length);
        
        success = sqlite3_step(statement);
        sqlite3_finalize(statement);
        
        if (success == SQLITE_ERROR) {
            NSLog(@"Error: failed to insert into the database with message.");
            return NO;
        } 
    }

    sqlite3_exec(database_, "commit transaction", NULL, NULL, &msg);
    return YES;
}

-(BOOL)getTxtCategory:(NSMutableArray *)category page:(NSString *)bookId {
    sqlite3_stmt *statement = nil;
	static char *sql = "select range_location, title from cjp_txtpages \
    where book_id = ? and book_orient = 'C'";
    //from cjp_book order by book_id desc limit ?, ?";
    
    if (sqlite3_prepare_v2(database_, sql, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement with message:get data.");
        return NO;
    }
    
    sqlite3_bind_text(statement, 1, [bookId UTF8String], -1, SQLITE_TRANSIENT);
    
    while (sqlite3_step(statement) == SQLITE_ROW) {
        [category addObject:
         [[NSArray alloc] initWithObjects:
          [NSString stringWithFormat:@"%d", sqlite3_column_int(statement, 0)],
          [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)], 
          nil]];
    }
    sqlite3_finalize(statement);
    
	return YES;
}

-(BOOL)setTxtCategory:(NSMutableArray *)category page:(NSString *)bookId orient:(NSString *)orient {
    char *msg;
    sqlite3_stmt *statement2 = nil;
	static char *sql2 = "select range_location, title from cjp_txtpages \
    where book_id = ? and book_orient = 'C' limit 5";
    //from cjp_book order by book_id desc limit ?, ?";
    
    if (sqlite3_prepare_v2(database_, sql2, -1, &statement2, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement with message:get data.");
        return NO;
    }
    
    sqlite3_bind_text(statement2, 1, [bookId UTF8String], -1, SQLITE_TRANSIENT);
    
    BOOL hasCategory = FALSE;
    if (sqlite3_step(statement2) == SQLITE_ROW) {
        hasCategory = TRUE;
    }
    sqlite3_finalize(statement2);
    
    if(!hasCategory){
        sqlite3_stmt *statement;
        static char *sql = "INSERT INTO cjp_txtpages (\
        book_id, book_orient, range_location, title) \
        VALUES(?,?,?,?)";
        
        sqlite3_exec(database_, "begin transaction", NULL, NULL, &msg);
        
        for (int i=0; i<[category count]; i++) {
            int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);
            if (success != SQLITE_OK) {
                NSLog(@"Error: failed to insert: cjp_textpages category");
                return NO;
            }
            
            //NSLog(@"insert index:%d", i);
            NSArray *a = [category objectAtIndex:i];
            
            sqlite3_bind_text(statement, 1, [bookId UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, 2, [orient UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_int(statement, 3, [[a objectAtIndex:0] intValue]);
            sqlite3_bind_text(statement, 4, [[a objectAtIndex:1] UTF8String], -1, SQLITE_TRANSIENT);
            
            success = sqlite3_step(statement);
            sqlite3_finalize(statement);
            
            if (success == SQLITE_ERROR) {
                NSLog(@"Error: failed to insert into the database with message.");
                return NO;
            } 
        }
        
        sqlite3_exec(database_, "commit transaction", NULL, NULL, &msg);
    }
    return YES;
}

-(BOOL)deleteTxtCategory {
    char *msg;
    sqlite3_stmt *statement;
    static char *sql = "delete from cjp_txtpages";
    
    sqlite3_exec(database_, "begin transaction", NULL, NULL, &msg);
    
    int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);
    if (success != SQLITE_OK) {
        NSLog(@"Error: failed to insert: cjp_textpages category");
        return NO;
    }
    success = sqlite3_step(statement);
    sqlite3_finalize(statement);
    
    if (success == SQLITE_ERROR) {
        NSLog(@"Error: failed to insert into the database with message.");
        return NO;
    } 
    
    sqlite3_exec(database_, "commit transaction", NULL, NULL, &msg);
    return YES;
}

-(BOOL)addTxtBookmark:(NSString *)bookId location:(int)location title:(NSString *)title {
    char *msg;
    sqlite3_stmt *statement;
    static char *sql = "INSERT INTO cjp_txtpages (\
    book_id, book_orient, range_location, title) \
    VALUES(?,?,?,?)";
    
    sqlite3_exec(database_, "begin transaction", NULL, NULL, &msg);
    
    int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);
    if (success != SQLITE_OK) {
        NSLog(@"Error: failed to insert: cjp_textpages add bookmark");
        return NO;
    }
    
    sqlite3_bind_text(statement, 1, [bookId UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 2, [@"M" UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(statement, 3, location);
    sqlite3_bind_text(statement, 4, [title UTF8String], -1, SQLITE_TRANSIENT);
    
    success = sqlite3_step(statement);
    sqlite3_finalize(statement);
    
    if (success == SQLITE_ERROR) {
        NSLog(@"Error: failed to insert into the database with message.");
        return NO;
    } 
    
    sqlite3_exec(database_, "commit transaction", NULL, NULL, &msg);
    
    return YES;
}

-(BOOL)deleteTxtBookmark:(NSString *)bookId location:(int)location length:(int)length {
    char *msg;
    sqlite3_stmt *statement;
    static char *sql = "delete from cjp_txtpages where book_orient = 'M' and \
    book_id = ? and range_location >= ? and range_location <= ?";
    
    sqlite3_exec(database_, "begin transaction", NULL, NULL, &msg);
    
    int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);
    if (success != SQLITE_OK) {
        NSLog(@"Error: failed to delete: cjp_textpages bookmark");
        return NO;
    }
    
    sqlite3_bind_text(statement, 1, [bookId UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(statement, 2, location);
    sqlite3_bind_int(statement, 3, location + length);
    
    success = sqlite3_step(statement);
    sqlite3_finalize(statement);
    
    if (success == SQLITE_ERROR) {
        NSLog(@"Error: failed to delete into the database with message.");
        return NO;
    } 
    
    sqlite3_exec(database_, "commit transaction", NULL, NULL, &msg);
    return YES;
}


-(BOOL)getTxtBookmark:(NSMutableArray *)bookmarks page:(NSString *)bookId {
    sqlite3_stmt *statement = nil;
	static char *sql = "select range_location, title from cjp_txtpages \
    where book_id = ? and book_orient = 'M'";
    //from cjp_book order by book_id desc limit ?, ?";
    
    if (sqlite3_prepare_v2(database_, sql, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement with message:get data.");
        return NO;
    }
    
    sqlite3_bind_text(statement, 1, [bookId UTF8String], -1, SQLITE_TRANSIENT);
    
    while (sqlite3_step(statement) == SQLITE_ROW) {
        [bookmarks addObject:
         [[NSArray alloc] initWithObjects:
          [NSString stringWithFormat:@"%d", sqlite3_column_int(statement, 0)],
          [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)], 
          nil]];
    }
    sqlite3_finalize(statement);
    
	return YES;
}

-(BOOL)deleteAds {
    char *msg;
    sqlite3_stmt *statement;
    static char *sql = "delete from cjp_ad";
    
    sqlite3_exec(database_, "begin transaction", NULL, NULL, &msg);
    
    int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);
    if (success != SQLITE_OK) {
        NSLog(@"Error: failed to delete: cjp_ad bookmark");
        return NO;
    }
    
    success = sqlite3_step(statement);
    sqlite3_finalize(statement);
    
    if (success == SQLITE_ERROR) {
        NSLog(@"Error: failed to delete into the database with message.");
        return NO;
    } 
    
    sqlite3_exec(database_, "commit transaction", NULL, NULL, &msg);
    return YES;
}

-(BOOL)getAds:(NSMutableArray *)ads {
    sqlite3_stmt *statement = nil;
	static char *sql = "select ad_id, ad_title, image_4_phone from cjp_ad where ad_state = '1'";
    //from cjp_book order by book_id desc limit ?, ?";
    
    if (sqlite3_prepare_v2(database_, sql, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement with message:get ads.");
        return NO;
    }
    
    while (sqlite3_step(statement) == SQLITE_ROW) {
        AdItem *adItem = [[AdItem alloc] init];
        [adItem setAdId:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)]];
        [adItem setAdTitle:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)]];
        [adItem setImage4Pad:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)]];
        [ads addObject:adItem];
        [adItem release];
    }
    sqlite3_finalize(statement);
    
	return YES;
}

-(BOOL)bookStateNull {
    char *msg;
    sqlite3_stmt *statement;
    static char *sql = "update cjp_book set book_state = null";
    
    sqlite3_exec(database_, "begin transaction", NULL, NULL, &msg);
    
    int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);
    if (success != SQLITE_OK) {
        NSLog(@"Error: failed to delete: cjp_ad bookmark");
        return NO;
    }
    
    success = sqlite3_step(statement);
    sqlite3_finalize(statement);
    
    if (success == SQLITE_ERROR) {
        NSLog(@"Error: failed to delete into the database with message.");
        return NO;
    } 
    
    sqlite3_exec(database_, "commit transaction", NULL, NULL, &msg);
    return YES;
}

//唯一键的问题导致数据重复，清理数据
-(BOOL)changeBookRight {
    //[self bookStateNull];
    //NSLog(@"changeBookRight");
    char *msg;
    sqlite3_stmt *statement = nil;
	static char *sqlSettingSelect = "select set_value, set_id from cjp_setting where set_id = 'book_right'";
	static char *sqlSettingInsert = "INSERT INTO cjp_setting (set_id, set_value) VALUES('book_right', '0')";
	static char *sqlSettingUpdate = "UPDATE cjp_setting SET set_value = '1' WHERE set_id = 'book_right'";
    
    sqlite3_exec(database_, "begin transaction", NULL, NULL, &msg);
    
    //检查标志参数
    int needUpdateBookRight = 1;//1:不需要更新；2:需要更新；3:需要更新且插入cjp_setting数据(book_right,1)
    //检查标志，如果Setting表的数据 book_right 为 1 则不处理，否则处理
    if (sqlite3_prepare_v2(database_, sqlSettingSelect, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to check cjp_setting.book_right value.");
        return NO;
    }
    
    if (sqlite3_step(statement) == SQLITE_ROW) {
        if( (char *)sqlite3_column_text(statement, 0) != NULL && 
           [[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)] isEqualToString:@"1"]) {
            //needUpdateBookRight = 1;
        }else{
            needUpdateBookRight = 2;
        }
    }else{
        needUpdateBookRight = 3;
    }
    sqlite3_finalize(statement);
    
    //needUpdateBookRight = 2;
    //NSLog(@"needUpdateBookRight = 2");
    //插入数据到setting表，以不再处理此事件
    if(needUpdateBookRight == 3) {
        int success1 = sqlite3_prepare_v2(database_, sqlSettingInsert, -1, &statement, NULL);
        if (success1 != SQLITE_OK) {
            NSLog(@"Error: failed to insert: cjp_setting.book_right=0 ");
            return NO;
        }
        success1 = sqlite3_step(statement);
        sqlite3_finalize(statement);
        
        if (success1 == SQLITE_ERROR) {
            NSLog(@"Error: failed to insert cjp_setting.book_right=0 into the database with message.");
            return NO;
        } 
    }
    
    //开始清理数据
    if(needUpdateBookRight == 2 || needUpdateBookRight == 3) {
        static char *sqlAllBooks = "select pk_id, book_id from cjp_book order by pk_id";
        
        if (sqlite3_prepare_v2(database_, sqlAllBooks, -1, &statement, NULL) != SQLITE_OK) {
            NSLog(@"Error: failed to prepare statement with message:get ads.");
            return NO;
        }
        
        NSMutableArray *allBooksArray = [[NSMutableArray alloc] initWithCapacity:20];
        
        while (sqlite3_step(statement) == SQLITE_ROW) {
            [allBooksArray addObject:
             [[NSArray alloc] initWithObjects:
              [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)], 
              [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)], nil ]
             ];
        }
        sqlite3_finalize(statement);
        //NSLog(@"allBooksArray:%d", [allBooksArray count]);
        
        if(allBooksArray != nil && [allBooksArray count] > 0) {
            static char *sqlBookUpdate = "UPDATE cjp_book SET book_state = '1' WHERE pk_id = ?";
            NSMutableDictionary *uniqueBooksDictionary = [[NSMutableDictionary alloc] initWithCapacity:20];
            for(int i = 0 ; i < [allBooksArray count]; i++) {
                NSArray *bookItem = [allBooksArray objectAtIndex:i];
                if([uniqueBooksDictionary objectForKey:[bookItem objectAtIndex:1]] == nil){
                    int success2 = sqlite3_prepare_v2(database_, sqlBookUpdate, -1, &statement, NULL);
                    if (success2 != SQLITE_OK) {
                        NSLog(@"Error: failed to update: cjp_book.book_state = 1");
                        return NO;
                    }
                    
                    sqlite3_bind_text(statement, 1, [[bookItem objectAtIndex:0] UTF8String], -1, SQLITE_TRANSIENT);
                    success2 = sqlite3_step(statement);
                    sqlite3_finalize(statement);
                    
                    if (success2 == SQLITE_ERROR) {
                        NSLog(@"Error: failed to update cjp_book.book_state=1 with message.");
                        return NO;
                    }
                    //NSLog(@"bookID:%@", [bookItem objectAtIndex:1]);
                    [uniqueBooksDictionary setObject:@"" forKey:[bookItem objectAtIndex:1]];
                }
            }
            //NSLog(@"uniqueBooksDictionary:%d", [uniqueBooksDictionary count]);
            [uniqueBooksDictionary release];
        }
        
        
        //更新标志状态
        int success3 = sqlite3_prepare_v2(database_, sqlSettingUpdate, -1, &statement, NULL);
        if (success3 != SQLITE_OK) {
            NSLog(@"Error: failed to update: cjp_setting.book_right = 1");
            return NO;
        }
        
        success3 = sqlite3_step(statement);
        sqlite3_finalize(statement);
        
        if (success3 == SQLITE_ERROR) {
            NSLog(@"Error: failed to update cjp_setting.book_right=1 with message.");
            return NO;
        }
        
        //删除重复的垃圾数据
        static char *sqlBookRepeatDelete = "delete from cjp_book where book_state is null";
        int success4 = sqlite3_prepare_v2(database_, sqlBookRepeatDelete, -1, &statement, NULL);
        if (success4 != SQLITE_OK) {
            NSLog(@"Error: failed to delete: cjp_book.book_state = null");
            return NO;
        }
        
        success4 = sqlite3_step(statement);
        sqlite3_finalize(statement);
        
        if (success4 == SQLITE_ERROR) {
            NSLog(@"Error: failed to delete cjp_book.book_state = null dustbin data with message.");
            return NO;
        }
    }
    
    sqlite3_exec(database_, "commit transaction", NULL, NULL, &msg);
    return YES;
}

/*
 news_id varchar2(32),\
 news_title varchar2(255),\
 news_type varchar2(64),\
 news_content text,\
 news_date varchar2(64),\
 */
- (BOOL) insertOrUpdate:(NSMutableArray *)news{
    BOOL isNewNews = FALSE;
    char *msg;
    sqlite3_stmt *statement;
    static char *sql = "INSERT INTO cjp_news (news_id, news_title, \
    news_type, news_content, news_date) VALUES(?,?,?,?,?)";
    
    static char *sqlcheck = "select news_id from cjp_news where news_id = ?";
    static char *sqlupdate = "update cjp_news set news_content = ? where news_id = ?";
    
    sqlite3_exec(database_, "begin transaction", NULL, NULL, &msg);
    
    for (int i=0; i<[news count]; i++) {
        News *newsItem = [news objectAtIndex:i];
        
        BOOL isExistNews = FALSE;
        
        //检查是否存在任务
        if (sqlite3_prepare_v2(database_, sqlcheck, -1, &statement, NULL) != SQLITE_OK) {
            NSLog(@"Error: failed to prepare statement with message:insert NEWS begin check.");
            return NO;
        }
        sqlite3_bind_text(statement, 1, [[newsItem newsId] UTF8String], -1, SQLITE_TRANSIENT);
        if (sqlite3_step(statement) == SQLITE_ROW) {
            isExistNews = TRUE;
        }
        sqlite3_finalize(statement);
        
        //如果本地表中不存在数据，则插入数据
        if(!isExistNews) {
            
            isNewNews = TRUE;
            
            int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);
            if (success != SQLITE_OK) {
                NSLog(@"Error: failed to insert: news");
                return NO;
            }

            int bindIndex = 1;
            sqlite3_bind_text(statement, bindIndex++, [(newsItem.newsId == nil ? @"" : newsItem.newsId) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(newsItem.newsTitle == nil ? @"" : newsItem.newsTitle) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(newsItem.newsType == nil ? @"" : newsItem.newsType) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(newsItem.newsContent == nil ? @"" : newsItem.newsContent) UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, bindIndex++, [(newsItem.newsDate == nil ? @"" : newsItem.newsDate) UTF8String], -1, SQLITE_TRANSIENT);
            
            
            success = sqlite3_step(statement);
            sqlite3_finalize(statement);
            
            if (success == SQLITE_ERROR) {
                NSLog(@"Error: failed to insert into NEWS the database with message.");
                return NO;
            }
        }
    }
    
    sqlite3_exec(database_, "commit transaction", NULL, NULL, &msg);
	return isNewNews;
}


//查询数据库推荐图书
-(BOOL)getAllNews:(NSMutableArray *)notices news:(NSMutableArray *)news {
	sqlite3_stmt *statement = nil;
	static char *sql = "select news_id, news_title, news_type, news_content, news_date from cjp_news";
    //order by book_id desc limit ?, ?";
    
    if (sqlite3_prepare_v2(database_, sql, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"Error: failed to prepare statement with message:get news data.");
        return NO;
    }
    
    while (sqlite3_step(statement) == SQLITE_ROW) {
        News *newsItem = [[News alloc] init];
        newsItem.newsId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
        newsItem.newsTitle = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
        newsItem.newsType = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
        newsItem.newsContent = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
        newsItem.newsDate = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];
        
        if([newsItem.newsType isEqualToString:@"公告"]) {
            [notices addObject:newsItem];
        } else {
            [news addObject:newsItem];
        }
        [newsItem release];
    }
    sqlite3_finalize(statement);
    
	return YES;
}
@end
